<?php
	/* various utility for ecoez
	 * 
	 * created by : michael.ocana
	 * on : march 2010
	 * 
	 */
	class EcoezUtility {
		
		public $size_codes = array("L","LA","LM","M","MA","MJ","S","SA","SJ","T");

		protected function setUp() {
			
			$this->size_codes = self::size_codes;
			
		}
		public function getSystems($organization_id) {
			Zend_Loader::loadClass("QueryCreator");
			Zend_Loader::loadClass("Tank");
			$query = new QueryCreator();
						
			$query->addSelect("system_name");
			$query->addFrom(Tank::_tablename . " as tank ");
			$query->addWhere("org_id = ".$organization_id);
			$query->addGroupBy(" system_name");
			
			$db = DBCon::instance();

			$result = $db->executeQuery($query->createSQL());
					
			return $result;
		}
		
		public function getSystemByTankId($tankID, $org_id) {
			Zend_Loader::loadClass("Tank");
			
			$systems = new Tank();
						
			$systems->loadWhere("tank_id = ".$tankID." AND org_id = ".$org_id);
					
			return $systems;
		}
		
		public function getTanksBySystem($system, $org_id) {
			Zend_Loader::loadClass("Tank");
			Zend_Loader::loadClass("QueryCreator");
			
			$query = new QueryCreator();					
			$tanks = new Tank();
			
			$query->addSelect("t.system_name, t.tank_id");
			$query->addFrom(Tank::_tablename . " as t ");
			$query->addWhere("system_name = '".$system."' AND org_id = ".$org_id);			
						
			$db = DBCon::instance();
			$tanks = $db->executeQuery($query->createSQL());
					
			return $tanks;
		}	
		
		public function getSystemByTankInspectionID($inspection_id, $org_id) {
			Zend_Loader::loadClass("Tank");
			Zend_Loader::loadClass("TankInspection");
					
			$system_t = new Tank();
			$ti = new TankInspection();
			
			$ti->loadWhere("tank_inspection_id = ".$inspection_id);			
			$system_t->loadWhere("tank_id = ".$ti->tank_id." AND org_id = ".$org_id);

			return $system_t->system_name;
		}			
		
		public function saveTankInspection($id, $tankID, $defaultValue, $userValue){
			Zend_Loader::loadClass("TankInspection");
			Zend_Loader::loadClass("TimeUtility");
				
			$inspection = new TankInspection();	
				
			$inspection->loadWhere(" tank_inspection_id = " . $defaultValue);
			$inspection->tank_id = $tankID;
			$inspection->date_inspected = $userValue['date_inspected'];
			$inspection->remarks = $userValue['remarks'];
			$inspection->result = $userValue['result'];
			$inspection->date_next_inspection = $userValue['date_next_inspection'];
			$inspection->calcium = $userValue['calcium'];
			$inspection->calcium_remarks = $userValue['calcium_remarks'];
			$inspection->kh = $userValue['kh'];
			$inspection->kh_remarks = $userValue['kh_remarks'];		
			$inspection->ammonia = $userValue['ammonia'];
			$inspection->ammonia_remarks = $userValue['ammonia_remarks'];
			$inspection->nitrite = $userValue['nitrite'];
			$inspection->nitrite_remarks = $userValue['nitrite_remarks'];
			$inspection->redox = $userValue['redox'];
			$inspection->redox_remarks = $userValue['redox_remarks'];								
			$inspection->nitrate = $userValue['nitrate'];
			$inspection->nitrate_remarks = $userValue['nitrate_remarks'];
			$inspection->salinity = $userValue['salinity'];
			$inspection->salinity_remarks = $userValue['salinity_remarks'];
			$inspection->temperature = $userValue['temperature'];
			$inspection->temperature_remarks = $userValue['temperature_remarks'];
			$inspection->additives = $userValue['additives'];
			$inspection->additives_remarks = $userValue['additives_remarks'];
			$inspection->bulb_change = $userValue['bulb_change'];
			$inspection->bulb_change_remarks = $userValue['bulb_change_remarks'];
			$inspection->pump_cleaning = $userValue['pump_cleaning'];
			$inspection->pump_cleaning_remarks = $userValue['pump_cleaning_remarks'];
			$inspection->uv_replacement = $userValue['uv_replacement'];
			$inspection->uv_replacement_remarks = $userValue['uv_replacement_remarks'];
			$inspection->water_change = $userValue['water_change'];
			$inspection->water_change_remarks = $userValue['water_change_remarks'];
			
			if(!$inspection->tank_inventory_id) {
				$inspection->date_created = TimeUtility::convertToSQLDateFormat(date('m/d/y H:i:s'));
				$inspection->created_by = $id;
			}
			$inspection->date_updated = TimeUtility::convertToSQLDateFormat(date('m/d/y H:i:s'));
			$inspection->updated_by = $id;

			$inspection->save();			
						
		}

		public function getOrganismsByOrgId($orgId, $orderingStatus="", $group="", $searchName="", $searchAlpha="", $reload="") {
			Zend_Loader::loadClass("Organization");
			Zend_Loader::loadClass("Organism");
			Zend_Loader::loadClass("OrganizationOrganism");
			Zend_Loader::loadClass("OrganismAttribute");
			Zend_Loader::loadClass("Size");
			Zend_Loader::loadClass("QueryCreator");
			
			$query = new QueryCreator();

//			$query->addSelect("o.*, oo.*");
//			$query->addSelect("replace(o.scientific_name,' ','-') as image  ");
//			$query->addFrom(Organism::_tablename. " as o ");
//			$query->addJoin(" left join ".OrganizationOrganism::_tablename." oo on o.organism_id = oo.organism_id ");
//			$query->addWhere(" oo.org_id = ".$orgId);

			$query->addSelect("o.*, oo.*");
			$query->addSelect("replace(o.scientific_name,' ','-') as image  ");
			$query->addSelect("s.size as size_name");
			$query->addFrom(Organism::_tablename. " as o ");
			$query->addJoin(" left join ".OrganizationOrganism::_tablename." oo on o.organism_id = oo.organism_id ");
			$query->addJoin(" left join ".OrganismAttribute::_tablename." as oa on oa.organism_id = o.organism_id ");
			$query->addJoin(" left join ".Size::_tablename." as s on s.size_code = oa.size_code ");
			$query->addWhere(" oo.org_id = ".$orgId);
			
			// Ordering Status
			if(strlen($orderingStatus) > 0 && $orderingStatus <> 'All') {
				$query->addWhere(" oo.ordering_status = '". $orderingStatus."'");
			}

			// group
			if(strlen($group) > 0) {
				$query->addWhere(" o.organism_group_id = " . $group);
			}

			// only name or first character
			if(strlen($searchName) > 0) {
				$query->addWhere(" o.name LIKE '%" . $searchName . "%'");	
			} else {
				// alpha
				if(strlen($searchAlpha) > 0) {
					if ($searchAlpha <> '#') {
						$query->addWhere("lower(left(o.name,1)) LIKE '". $searchAlpha . "%'");
					}
				}
			}
			$query->addGroupBy("oo.org_organism_id");

			
			$db = DBCon::instance();
			$result = $db->executeQuery($query->createSQL());
			
			return $result;
		}
			
		public function add_organisms_attributes_to_organization($organization_id) {
			Zend_Loader::loadClass("Organism");
			Zend_Loader::loadClass("OrganizationOrganism");
			Zend_Loader::loadClass("TimeUtility");
			Zend_Loader::loadClass("QueryCreator");
			
			$db = DBCon::instance();
			$query = new QueryCreator();
			$organisms = new Organism();
			
			$query->addSelect("o.organism_id");
			$query->addFrom(Organism::_tablename." as o");		

			$organisms = $db->executeQuery($query->createSQL());

			$size_codes = array("L","LA","LM","M","MA","MJ","S","SA","SJ","T");
			$status = "Active";
			$currency = "Kina";
			$base_price_sale = 1;
			$ordering_status = "Active";
			$unsuitable = 0;
			$created_by = "migrator";
			
			$counter_processed = 0;
			$counter_not_processed = 0;
			print "<pre>";
			
			foreach ($organisms as $organism) {
				foreach($size_codes as $size_code) {		
				
					$attributes = $this->get_attribute_id($organism['organism_id'], $size_code);	
					
					if (count($attributes) > 0) {	
						$attribute_id = $attributes[0]['organism_attribute_id'];
						$check_value = $this->check_organism_if_exist_in_organization($organism['organism_id'], $organization_id, $attribute_id);
					} else {						
						echo "No attributes found : ".$organism['organism_id']." , size : ".$size_code."\r\n";
						echo "   adding the new attribute...\r\n";
						$this->add_organism_attributes_by_size_code($organism['organism_id'], $size_code);
						
						$attributes = $this->get_attribute_id($organism['organism_id'], $size_code);
						
						$attribute_id = $attributes[0]['organism_attribute_id'];
						$check_value = $this->check_organism_if_exist_in_organization($organism['organism_id'], $organization_id, $attribute_id);												
					}						
					
					if (count($check_value) > 0) {
						//echo "Already in the orgranization...".$organism['organism_id']."\r\n";
						$counter_not_processed = $counter_not_processed + 1;
					} else {
													
						echo "<pre>Adding in the orgranization...".$organism['organism_id']." </pre>";
						$attributes = $this->get_attribute_id($organism['organism_id'], $size_code);
						if (count($attributes) > 0) {								
							//$result = $this->save_to_organization_organisms();
							$organization = new OrganizationOrganism();
							$organization->organism_id = $organism['organism_id'];
							$organization->currency_mark = $currency;
							$organization->organism_attribute_id = $attribute_id;
							$organization->created_by = $created_by;
							$organization->unsuitable = $unsuitable;
							$organization->date_created = TimeUtility::convertToSQLDateFormat(date('m/d/y H:i:s'));
							$organization->base_price_sale = $base_price_sale;
							$organization->ordering_status  = $status;
							$organization->org_id  = $organization_id;;
							$organization->save();
							$organization->org_organism_id  = $organization->id;
							$organization->save();								
							$counter_processed = $counter_processed + 1;
							echo "&nbsp;&nbsp; org_organism_id : ".$organization->org_organism_id.", organism_id : ".$organization->organism_id.", size : ".$size_code."\r\n";
						}
					}

				}
			}	
			print "\r\n";
			print "No of organism processed and added to the organization table : ".$counter_processed."\r\n";
			print "No of organism not processed or it's already in the organization table : ".$counter_not_processed."\r\n";
			print "\r\n"."done...";	
			print "</pre>";			
		}
		function add_organism_attributes_by_size_code($organism_id, $size_code) {
			Zend_Loader::loadClass("OrganismAttribute");
			Zend_Loader::loadClass("TimeUtility");
			
			$attribute = new OrganismAttribute();
								
			$dResult = $attribute->loadWhere("organism_id = ".$organism_id." AND size_code = '".$size_code."'");	
			if ($dResult) {
				// dont do anything					
			} else {
				$attribute->organism_id = $organism_id;
				$attribute->size_code = $size_code;
				$attribute->default_price_sale = 1;
				$attribute->estimated_length = 0;
				$attribute->date_created = TimeUtility::convertToSQLDateFormat(date('m/d/y H:i:s'));
				$attribute->date_updated = TimeUtility::convertToSQLDateFormat(date('m/d/y H:i:s'));
				$attribute->created_by = "migrator";
				$attribute->updated_by = "migrator";
				$attribute->save();
				$attribute->organism_attribute_id = $attribute->id;
				$attribute->save();
			}

			return $attribute;			
		}
		function add_organism_attributes($organism_id) {
			Zend_Loader::loadClass("OrganismAttribute");
			Zend_Loader::loadClass("TimeUtility");
			$size_codes = array("L","LA","LM","M","MA","MJ","S","SA","SJ","T");
			
			$attribute = new OrganismAttribute();
						
			foreach($size_codes as $size_code) {			
				$dResult = $attribute->loadWhere("organism_id = ".$organism_id." AND size_code = '".$size_code."'");	
				if ($dResult) {
					// dont do anything					
				} else {
					$attribute->organism_id = $organism_id;
					$attribute->size_code = $size_code;
					$attribute->default_price_sale = 1;
					$attribute->estimated_length = 0;
					$attribute->date_created = TimeUtility::convertToSQLDateFormat(date('m/d/y H:i:s'));
					$attribute->date_updated = TimeUtility::convertToSQLDateFormat(date('m/d/y H:i:s'));
					$attribute->created_by = "migrator";
					$attribute->updated_by = "migrator";
					$attribute->save();
					$attribute->organism_attribute_id = $attribute->id;
					$attribute->save();
				}

			}
			return $attribute;
		}
		function check_organism_if_exist_in_organization($organism_id, $organization_id, $attribute_id) {
			Zend_Loader::loadClass("OrganizationOrganism");
			$attributes = new OrganizationOrganism();
			Zend_Loader::loadClass("QueryCreator");
						
			$db = DBCon::instance();
			$query = new QueryCreator();
			
			$query->addSelect("organism_id");
			$query->addFrom(OrganizationOrganism::_tablename);
			$query->addWhere("organism_id = ".$organism_id." AND org_id = ".$organization_id." AND organism_attribute_id = ".$attribute_id);
			
			$result = $db->executeQuery($query->createSQL());
			return $result;
		}
		
		function get_attribute_id($organism_id, $size_code) {
			Zend_Loader::loadClass("OrganismAttribute");
			$attributes = new OrganismAttribute();
			Zend_Loader::loadClass("QueryCreator");
						
			$db = DBCon::instance();
			$query = new QueryCreator();
			
			$query->addSelect("organism_attribute_id");
			$query->addFrom(OrganismAttribute::_tablename);
			$query->addWhere("organism_id = ".$organism_id." AND size_code  ='".$size_code."'");
			
			$result = $db->executeQuery($query->createSQL());
			return $result;			
		}
		
		/* migrate_key : aotti
		 * make sure before running this script you already run the 
		 * $this->add_organisms_attributes_to_organization()
		 */
		public function add_organisms_to_tank_inventory($organization_id) {
			Zend_Loader::loadClass("OrganismAttribute");
			Zend_Loader::loadClass("OrganizationOrganism");
			Zend_Loader::loadClass("TimeUtility");
			Zend_Loader::loadClass("Inventory");
			Zend_Loader::loadClass("QueryCreator");
			
			$default_tank = 16;  //FS 1.3
			$default_quantiy = 50;  //FS 1.3
					
			$db = DBCon::instance();
			$query = new QueryCreator();			
			
			$query->addSelect("oo.*");
			$query->addFrom(OrganizationOrganism::_tablename." oo");
			$query->addWhere("oo.org_id = ".$organization_id);
			
			$results = $db->executeQuery($query->createSQL());
			
			print "<pre>";
			print "\r\n"."Checking organism in organization...";
			$cntr_not_processed = 0;
			$cntr_processed = 0;
			foreach($results as $result) {
				$qryInventory = new Inventory();		

				$qryInventory->loadWhere("org_id = ".$result['org_id']." AND org_organism_id = ".$result['org_organism_id']." AND organism_attribute_id = ".$result['organism_attribute_id']);
				if (count($qryInventory->toArray()) < 1) {
					print "\r\n"."    adding organism : ".$result['organism_id']." in default tank : ".$default_tank." with ".$default_quantiy." pcs.";
					$qryInventory->org_id = $result['org_id'];
					$qryInventory->tank_id = $default_tank;
					$qryInventory->org_organism_id = $result['org_organism_id'];
					$qryInventory->organism_attribute_id = $result['organism_attribute_id'];
					$qryInventory->quantity_on_hand = $default_quantiy;
					$qryInventory->stock_amount = $default_quantiy;
					$qryInventory->currency_mark = "Kina";
					$qryInventory->date_updated = TimeUtility::convertToSQLDateFormat(date('m/d/y H:i:s'));
					$qryInventory->date_created = TimeUtility::convertToSQLDateFormat(date('m/d/y H:i:s'));
					$qryInventory->created_by = "migrator";
					$qryInventory->updated_by = "migrator";
					$qryInventory->save();
					$cntr_processed = $cntr_processed + 1;
				}	else {
					$cntr_not_processed = $cntr_not_processed + 1;
				}

				$qryInventory->clear();
			}
			print "\r\n";
			print "No of organism processed and added to the inventory table : ".$cntr_processed."\r\n";
			print "No of organism not processed or it's already in the inventory table : ".$cntr_not_processed."\r\n";
			print "\r\n"."done...";	
						
			print "</pre>";
			$rows = "";//$results;
			return $rows;
			
		}
		
		/* migrate_key : cuoa
		 * Delete duplicates in the organism attributes
		 * logic : search all organization organisms and store all duplicates in array
		 */
		public function clean_up_organism_attributes($organization_id) {
			Zend_Loader::loadClass("Organism");
			Zend_Loader::loadClass("Organism");
			Zend_Loader::loadClass("OrganizationOrganism");
			Zend_Loader::loadClass("OrganismAttribute");
			
			Zend_Loader::loadClass("QueryCreator");
			$db = DBCon::instance();			
			
			$query = new QueryCreator();
			
			$query->addSelect("o.*");
			$query->addFrom(Organism::_tablename." o");
			$organisms = $db->executeQuery($query->createSQL());	

			print "<pre>";
			print "\r\n"."Checking organism in organization...";			
			foreach($organisms as $key => $organism) {
				$sflag = 0; $size_delete = array();
				$mflag = 0; $lflag = 0; $tflag = 0;
				$saflag = 0; $sjflag = 0; $maflag = 0; $mjflag = 0;
				$laflag = 0; $ljflag = 0;
				$qryOrg = new QueryCreator();				
				$qryOrg->addSelect("oo.*, oa.size_code");
				$qryOrg->addFrom(OrganizationOrganism::_tablename." oo");
				$qryOrg->addJoin("LEFT JOIN ".OrganismAttribute::_tablename." oa on oo.organism_attribute_id = oa.organism_attribute_id");
				$qryOrg->addWhere("oo.org_id = ".$organization_id. " AND oo.organism_id = ".$organism['organism_id']);
				
				$attributes = $db->executeQuery($qryOrg->createSQL());	
				print "\r\n"."   Checking duplicates for organism_id : ".$organism['organism_id'];	
				foreach($attributes as $key2 => $attribute) {
					if ($attribute['size_code'] == "S") {
						if ($sflag) {
							$size_delete['Small'] = array("organism_attribute_id" => $attribute['organism_attribute_id'],
											"organism_id" => $attribute['organism_id'], "size_code" => $attribute['size_code']
											);
							$sflag = 0;				
						} else {$sflag = 1;}
					}
					if ($attribute['size_code'] == "M") {
						if ($mflag) {
							$size_delete['Medium'] = array("organism_attribute_id" => $attribute['organism_attribute_id'],
											"organism_id" => $attribute['organism_id'], "size_code" => $attribute['size_code']
											);
							$mflag = 0;		
						} else {$mflag = 1;}
					}	
					if ($attribute['size_code'] == "L") {
						if ($mflag) {
							$size_delete['Large'] = array("organism_attribute_id" => $attribute['organism_attribute_id'],
											"organism_id" => $attribute['organism_id'], "size_code" => $attribute['size_code']
											);
							$mflag = 0;		
						} else {$mflag = 1;}
					}		
					if ($attribute['size_code'] == "T") {
						if ($tflag) {
							$size_delete['Tiny'] = array("organism_attribute_id" => $attribute['organism_attribute_id'],
											"organism_id" => $attribute['organism_id'], "size_code" => $attribute['size_code']
											);
							$tflag = 0;		
						} else {$tflag = 1;}
					}	
					if ($attribute['size_code'] == "LA") {
						if ($laflag) {
							$size_delete['LargeAdult'] = array("organism_attribute_id" => $attribute['organism_attribute_id'],
											"organism_id" => $attribute['organism_id'], "size_code" => $attribute['size_code']
											);
							$laflag = 0;		
						} else {$laflag = 1;}
					}		
					if ($attribute['size_code'] == "LJ") {
						if ($ljflag) {
							$size_delete['LargeJuvenile'] = array("organism_attribute_id" => $attribute['organism_attribute_id'],
											"organism_id" => $attribute['organism_id'], "size_code" => $attribute['size_code']
											);
							$ljflag = 0;		
						} else {$ljflag = 1;}
					}		
					if ($attribute['size_code'] == "MA") {
						if ($maflag) {
							$size_delete['MediumAdult'] = array("organism_attribute_id" => $attribute['organism_attribute_id'],
											"organism_id" => $attribute['organism_id'], "size_code" => $attribute['size_code']
											);
							$maflag = 0;		
						} else {$maflag = 1;}
					}	
					if ($attribute['size_code'] == "MJ") {
						if ($mjflag) {
							$size_delete['MediumJuvenile'] = array("organism_attribute_id" => $attribute['organism_attribute_id'],
											"organism_id" => $attribute['organism_id'], "size_code" => $attribute['size_code']
											);
							$mjflag = 0;		
						} else {$mjflag = 1;}
					}	
					if ($attribute['size_code'] == "SA") {
						if ($saflag) {
							$size_delete['SmallAdult'] = array("organism_attribute_id" => $attribute['organism_attribute_id'],
											"organism_id" => $attribute['organism_id'], "size_code" => $attribute['size_code']
											);
							$saflag = 0;		
						} else {$saflag = 1;}
					}		
					if ($attribute['size_code'] == "SJ") {
						if ($sjflag) {
							$size_delete['SmallJuvenile'] = array("organism_attribute_id" => $attribute['organism_attribute_id'],
											"organism_id" => $attribute['organism_id'], "size_code" => $attribute['size_code']
											);
							$sjflag = 0;		
						} else {$sjflag = 1;}
					}																																		
				}
						
				$qryOrg->clear();
				$this->delete_duplicated_attributes($size_delete);
		
			}
			print "\r\n"."Done...";
			print "\r\n";	
			print "</pre>";				
			return 1;			
		}
		function delete_duplicated_attributes($duplicate_sizes) {
			Zend_Loader::loadClass("OrganizationOrganism");
			Zend_Loader::loadClass("OrganismAttribute");	

			$sizes = array("Small", "Medium", "Large", "Tiny", "LargeAdult", "LargeJuvenile", "MediumAdult", "MediumJuvenile", "SmallAdult", "SmallJuvenile");
			// small			
			//foreach ($sizes as $size) {				
				foreach ($duplicate_sizes as $key => $duplicate_size) {	
			        print "\r\n"."      Deleting ".$sizes[0]." duplicates : ";
					$sql = "DELETE FROM ".OrganizationOrganism::_tablename." WHERE organism_attribute_id = ".$duplicate_size['organism_attribute_id']." AND organism_id = ".$duplicate_size['organism_id'];
						
			        $db = DBCon::instance();
			        $db->executeCommand($sql);										
					
					$where = " WHERE organism_attribute_id = ".$duplicate_size['organism_attribute_id']." AND organism_id = ".$duplicate_size['organism_id'];
			        $sql = "DELETE FROM ".OrganismAttribute::_tablename.$where;
					
			        $db = DBCon::instance();
			        $db->executeCommand($sql);				
				}				
			//}
				
			return 1;
		}
		
	}
?>